import os
from datetime import datetime, timedelta, timezone


def deduplicate_dicts(dict_list, keys):
    seen = set()
    unique_dicts = []

    for d in dict_list:
        # Create a tuple of the specified keys for each dictionary
        key_tuple = tuple(d[key] for key in keys)

        # Check if this combination of keys has been seen before
        if key_tuple not in seen:
            seen.add(key_tuple)
            unique_dicts.append(d)

    return unique_dicts


f4=open('data.txt', encoding='utf-8')
#此时只读取了一行
pre = f4.readline()
pre = pre.replace("\n","")

data_list = list()
parts = pre.split("\t")
data_list.append(dict(pid=parts[0], flaws = parts[1].split(",")))
#利用循环全部读出
i = 1
while pre:
    next_line = f4.readline()
    next_line = next_line.replace("\n", "")
    if next_line:
        parts = next_line.split("\t")
        data_list.append(dict(pid=parts[0], flaws=parts[1].split(",")))
    pre = next_line
f4.close()


where_params = list()
for item in data_list:
    pid = item["pid"]
    flaws = item["flaws"]
    for flaw in flaws:
        ps = flaw.split("-")[0].split("_")
        device_id = ps[0]
        device_area = ps[1]
        where_params.append(dict(pid=pid, device_id=device_id, device_area=device_area))


all_res = deduplicate_dicts(dict_list=where_params, keys=["pid", "device_id","device_area"])

all_where_params = list()
for item in all_res:
    where = f"select * from imgs_l1产线_2024_12 where product_id='{item['pid']}' and device_id={item['device_id']} and area_id={item['device_area']}"
    all_where_params.append("\n")
    all_where_params.append(where)
    all_where_params.append("\n")
print(f"----------all_where_params: {' UNION '.join(all_where_params)}")